﻿using System;
using System.Collections.Generic;
using IndoReputation.Models.BusinessObject;
using System.Data.SqlClient;
using System.Data;
using IndoReputation.Models.BusinessObject.Collections;

namespace IndoReputation.Models.DataAccess
{
    public class RegionDAL
    {
        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:39 AM
        /// Todo: Insert a Object User to Database
        /// </summary>
        /// <param name="region">A region.</param>
        /// <returns></returns>
        public static int Insert(Region region)
        {
            const string spName = "Region_Insert";
            var spPara = new SqlParameter[8];
            spPara[0] = new SqlParameter("@ID", SqlDbType.Int, 2, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, null);
            if (region.RegionPID == null)
            {
                spPara[1] = new SqlParameter("@RegionPID", DBNull.Value);
            }
            else
            {
                spPara[1] = new SqlParameter("@RegionPID", region.RegionPID);
            }
            spPara[2] = new SqlParameter("@Name", region.Name);
            spPara[3] = new SqlParameter("@Description", region.Description);
            spPara[4] = new SqlParameter("@CreateAt", region.CreateAt);
            spPara[5] = new SqlParameter("@CreateBy", region.CreateBy);
            spPara[6] = new SqlParameter("@UpdateAt", DBNull.Value);
            spPara[7] = new SqlParameter("@UpdateBy", DBNull.Value);
            DataAccessBase.ExecuteNonQuery(CommandType.StoredProcedure, spName, spPara);
            return Convert.ToInt32(spPara[0].Value);
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:45 AM
        /// Todo: Update a User Exists in Database 
        /// </summary>
        /// <param name="region">A region.</param>
        /// <returns></returns>
        public static bool Update(Region region)
        {
            try
            {
                const string spName = "Region_UpdateByPK";
                var spPara = new SqlParameter[8];
                spPara[0] = new SqlParameter("@ID", region.ID);
                if (region.RegionPID == null)
                {
                    spPara[1] = new SqlParameter("@RegionPID", DBNull.Value);
                }
                else
                {
                    spPara[1] = new SqlParameter("@RegionPID", region.RegionPID);
                }
                spPara[2] = new SqlParameter("@Name", region.Name);
                spPara[3] = new SqlParameter("@Description", region.Description);
                spPara[4] = new SqlParameter("@CreateAt", region.CreateAt);
                spPara[5] = new SqlParameter("@CreateBy", region.CreateBy);
                spPara[6] = new SqlParameter("@UpdateAt", DBNull.Value);
                spPara[7] = new SqlParameter("@UpdateBy", DBNull.Value);
                var totalRecordEffected = 0;
                var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                return totalRecordEffected > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:36 AM
        /// Todo: Delete a Region Existst in Database
        /// </summary>        
        /// <param name="regionID"> </param>
        /// <returns></returns>
        public static bool Delete(int regionID)
        {
            try
            {
                const string spName = "Region_DeleteByPK";
                var spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", regionID);
                var totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                return totalRecordEffected > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static RegionList GetAllRegion()
        {
            RegionList listRegion = null;
            const string spName = "Region_SelectAll";
            var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0] != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            listRegion = new RegionList();
                            var listTemp = ds.Tables[0].ConvertToList<Region>();
                            listRegion.AddRange(listTemp);
                            //listRegion = (RegionList)DataAccessBase.ConvertToList<Region>(ds.Tables[0]);
                        }
                    }
                }
            }
            return listRegion;
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:40 AM
        /// Todo: Select a User existst in Database
        /// </summary>
        /// <param name="regionID">The user ID.</param>
        /// <returns></returns>
        public static Region GetRegionById(int regionID)
        {
            Region region = null;
            const string spName = "Region_SelectByPK";
            var spPara = new SqlParameter[1];
            spPara[0] = new SqlParameter("@ID", regionID);
            var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0] != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            region = ds.Tables[0].Rows[0].ConvertToEntity<Region>();
                        }
                    }
                }
            }
            return region;
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static List<Region> GetAllRegionParent()
        {
            //RegionList listRegion = null;
            const string spName = "Region_SelectAllRegionParent";
            var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0] != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            return ds.Tables[0].ConvertToList<Region>();
                        }
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static RegionJoinList GetAllRegionJoin()
        {
            RegionJoinList listRegion = null;
            const string spName = "Region_SelectAllJoin";
            var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0] != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            listRegion = new RegionJoinList();
                            var listTemp = ds.Tables[0].ConvertToList<RegionJoin>();
                            listRegion.AddRange(listTemp);
                        }
                    }
                }
            }
            return listRegion;
        }
    }
}